import pandas as pd
import seaborn as sns

import matplotlib.pyplot as plt

# 设置中文显示
plt.rcParams["font.family"] = ["SimHei"]
sns.set(font_scale=1.2)
sns.set_style("whitegrid")

plt.rcParams["font.family"] = ["SimHei"]
# 解决负号显示问题（可选）
plt.rcParams["axes.unicode_minus"] = False  # 正确显示负号

class QueryHandler:
    """查询处理器，提供数据查询和可视化功能"""

    def __init__(self, data_accessor, metrics_df):
        self.data_accessor = data_accessor
        self.metrics_df = metrics_df
        self.regions_df = data_accessor.get_regions()

    def get_region_metrics(self, region_name, period=None):
        """获取特定区域的业务指标"""
        region_id = self.data_accessor.get_region_id(region_name)

        if period:
            return self.metrics_df[(self.metrics_df["region_id"] == region_id) &
                                   (self.metrics_df["period"] == period)]
        else:
            return self.metrics_df[self.metrics_df["region_id"] == region_id]

    def compare_regions(self, metric_name, period):
        """比较不同区域的特定指标"""
        merged_df = pd.merge(
            self.metrics_df[(self.metrics_df["metric_name"] == metric_name) &
                            (self.metrics_df["period"] == period)],
            self.regions_df[["region_id", "region_name"]],
            on="region_id"
        )
        return merged_df.sort_values(by="metric_value", ascending=False)

    def get_top_performing_regions(self, metric_name, period, top_n=3):
        """获取特定指标表现最好的前N个区域"""
        comparison = self.compare_regions(metric_name, period)
        return comparison.head(top_n)

    def get_bottom_performing_regions(self, metric_name, period, bottom_n=3):
        """获取特定指标表现最差的后N个区域"""
        comparison = self.compare_regions(metric_name, period)
        return comparison.tail(bottom_n).sort_values(by="metric_value", ascending=True)

    def plot_region_metric_trend(self, region_name, metric_name):
        """绘制特定区域特定指标的趋势图"""
        region_data = self.get_region_metrics(region_name)
        metric_trend = region_data[region_data["metric_name"] == metric_name]

        plt.figure(figsize=(12, 6))
        sns.lineplot(data=metric_trend, x="period", y="metric_value", marker="o")
        plt.title(f"{region_name} {metric_name}趋势图")
        plt.xlabel("时间周期")
        plt.ylabel(f"{metric_name} (%)")
        plt.xticks(rotation=45)
        plt.tight_layout()
        plt.show()

    def plot_regions_comparison(self, metric_name, period):
        """绘制不同区域特定指标的对比图"""
        comparison_data = self.compare_regions(metric_name, period)

        plt.figure(figsize=(12, 6))
        sns.barplot(data=comparison_data, x="region_name", y="metric_value")
        plt.title(f"{period} 各区域{metric_name}对比")
        plt.xlabel("区域")
        plt.ylabel(f"{metric_name} (%)")
        plt.xticks(rotation=45)

        # 在柱状图上添加数值
        for i, v in enumerate(comparison_data["metric_value"]):
            plt.text(i, v + 0.5, f"{v:.2f}%", ha='center')

        plt.tight_layout()
        plt.show()

    # def export_to_excel(self, filename="insurance_business_metrics.xlsx"):
    #     """将所有数据导出到Excel"""
    #     with pd.ExcelWriter(filename) as writer:
    #         self.regions_df.to_excel(writer, sheet_name="区域信息", index=False)
    #         self.data_accessor.get_contracts().to_excel(writer, sheet_name="合同数据", index=False)
    #         self.data_accessor.get_assets().to_excel(writer, sheet_name="资产数据", index=False)
    #         self.metrics_df.to_excel(writer, sheet_name="业务指标", index=False)
    #
    #     print(f"数据已成功导出到 {filename}")

    def generate_region_report(self, region_name, period=None):
        """生成特定区域的业务指标报告"""
        if not period:
            period = self.data_accessor.get_latest_period()

        region_metrics = self.get_region_metrics(region_name, period)
        region_info = self.regions_df[self.regions_df["region_name"] == region_name].iloc[0]

        print(f"\n===== {region_name} 业务指标报告 =====")
        print(f"区域经理: {region_info['manager_name']}")
        print(f"报告周期: {period}")
        print("-------------------------------------")

        # 按业务类型分组展示指标
        business_types = set(region_metrics["applicable_business"].unique())
        for business in business_types:
            print(f"\n【{business}】")
            business_metrics = region_metrics[region_metrics["applicable_business"].str.contains(business)]

            for _, row in business_metrics.iterrows():
                print(f"{row['metric_name']}: {row['metric_value']}% - {row['metric_description']}")

        print("\n=====================================")
